#!/bin/bash

APP=edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
#一共需要输入两个参数，第一个参数是表名，如果是all就是所有的表，
#第二个参数是日期，如果不输默认是当前系统日期的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

ads_user_change="
insert overwrite table ${APP}.ads_user_change
select * from ${APP}.ads_user_change
union
select
    churn.dt,
    user_churn_count,
    user_back_count
from
(
    select
        '$do_date' dt,
        count(*) user_churn_count
    from ${APP}.dws_user_user_login_td
    where dt='$do_date'
    and login_date_last=date_add('$do_date',-7)
)churn
join
(
    select
        '$do_date' dt,
        count(*) user_back_count
    from
    (
        select
            user_id,
            login_date_last
        from ${APP}.dws_user_user_login_td
        where dt='$do_date'
    )t1
    join
    (
        select
            user_id,
            login_date_last login_date_previous
        from ${APP}.dws_user_user_login_td
        where dt=date_add('$do_date',-1)
    )t2
    on t1.user_id=t2.user_id
    where datediff(login_date_last,login_date_previous)>=8
)back
on churn.dt=back.dt;
"

ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
select * from ${APP}.ads_user_retention
union
select
    '$do_date' dt,
    login_date_first create_date,
    datediff('$do_date',login_date_first) retention_day,
    sum(if(login_date_last='$do_date',1,0)) retention_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
(
    select
        user_id,
        date_id login_date_first
    from ${APP}.dwd_user_register_inc
    where dt>=date_add('$do_date',-7)
    and dt<'$do_date'
)t1
join
(
    select
        user_id,
        login_date_last
    from ${APP}.dws_user_user_login_td
    where dt='$do_date'
)t2
on t1.user_id=t2.user_id
group by login_date_first;
"

ads_trade_stats="
insert overwrite table ${APP}.ads_trade_stats
select * from ${APP}.ads_trade_stats
union
select
    '$do_date' dt,
    odr.recent_days,
    order_total_amount,
    order_count,
    order_user_count
from
(
    select
        1 recent_days,
        sum(final_total_amount_1d) order_total_amount,
        sum(order_count_1d) order_count,
        count(*) order_user_count
    from ${APP}.dws_trade_user_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(order_total_amount),
        sum(order_count),
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then final_total_amount_7d
                when 30 then final_total_amount_30d
            end order_total_amount,
            case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
        from ${APP}.dws_trade_user_order_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)odr;"

ads_order_by_province="
insert overwrite table ${APP}.ads_order_by_province
select * from ${APP}.ads_order_by_province
union
select
    '$do_date' dt,
    1 recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
	iso_3166_2,
    order_count_1d,
    order_total_amount_1d
from ${APP}.dws_trade_province_order_1d
where dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
	iso_3166_2,
    sum(order_count),
    sum(order_total_amount)
from
(
    select
        recent_days,
        province_id,
        province_name,
        area_code,
        iso_code,
		iso_3166_2,
        case recent_days
            when 7 then order_count_7d
            when 30 then order_count_30d
        end order_count,
        case recent_days
            when 7 then order_total_amount_7d
            when 30 then order_total_amount_30d
        end order_total_amount
    from ${APP}.dws_trade_province_order_nd lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
)t1
group by recent_days,province_id,province_name,area_code,iso_code,iso_3166_2;"

ads_traffic_stats_by_channel="
insert overwrite table ${APP}.ads_traffic_stats_by_channel
select *
from ${APP}.ads_traffic_stats_by_channel
union
select '$do_date'                                                        dt,
       rencent_days,
       channel,
       cast(count(distinct (mid_id)) as bigint)                            uv_count,
       cast(avg(during_time_1d) / 1000 as bigint)                          avg_duration_sec,
       cast(avg(page_count_1d) as bigint)                                  avg_page_count,
       cast(count(*) as bigint)                                            sv_count,
       cast(sum(if(page_count_1d = 1, 1, 0)) / count(*) as decimal(16, 2)) bounce_rate
from ${APP}.dws_traffic_session_page_view_1d
         lateral view explode(array(1, 7, 30)) tmp as rencent_days
where dt >= date_sub('$do_date', rencent_days - 1)
group by rencent_days, channel;"

ads_page_path="
insert overwrite table ${APP}.ads_page_path
select * from ${APP}.ads_page_path
union
select
    '$do_date' dt,
    source,
    nvl(target,'null'),
    count(*) path_count
from
(
    select
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target
    from
    (
        select
            page_id,
            lead(page_id,1,null) over(partition by session_id order by view_time) next_page_id,
            row_number() over (partition by session_id order by view_time) rn
        from ${APP}.dwd_traffic_page_view_inc
        where dt='$do_date'
    )t1
)t2
group by source,target;"

ads_user_action="
insert overwrite table ${APP}.ads_user_action
select * from ${APP}.ads_user_action
union
select
    '$do_date' dt,
    page.recent_days,
    home_count,
    good_detail_count,
    cart_count,
    order_count,
    payment_count
from
(
    select
        1 recent_days,
        sum(if(page_id='home',1,0)) home_count,
        sum(if(page_id='good_detail',1,0)) good_detail_count
    from ${APP}.dws_traffic_page_visitor_page_view_1d
    where dt='2020-07-29'
    and page_id in ('home','good_detail')
    union all
    select
        recent_days,
        sum(if(page_id='home' and view_count>0,1,0)),
        sum(if(page_id='good_detail' and view_count>0,1,0))
    from
    (
        select
            recent_days,
            page_id,
            case recent_days
                when 7 then view_count_7d
                when 30 then view_count_30d
            end view_count
        from ${APP}.dws_traffic_page_visitor_page_view_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
        and page_id in ('home','good_detail')
    )t1
    group by recent_days
)page
join
(
    select
        1 recent_days,
        count(*) cart_count
    from ${APP}.dws_trade_user_cart_add_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(cart_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then cart_add_count_7d
                when 30 then cart_add_count_30d
            end cart_count
        from ${APP}.dws_trade_user_cart_add_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)cart
on page.recent_days=cart.recent_days
join
(
    select
        1 recent_days,
        count(*) order_count
    from dws_trade_user_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
        from ${APP}.dws_trade_user_order_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)ord
on page.recent_days=ord.recent_days
join
(
    select
        1 recent_days,
        count(*) payment_count
    from ${APP}.dws_trade_user_payment_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then payment_count_7d
                when 30 then payment_count_30d
            end order_count
        from ${APP}.dws_trade_user_payment_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)pay
on page.recent_days=pay.recent_days;"
ads_video_stats_by_chapter="
insert overwrite table ${APP}.ads_video_stats_by_chapter
select * from ${APP}.ads_video_stats_by_chapter
union
select
    dt,
    1 recent_days,
    chapter_id,
    chapter_name,
    video_count_1d video_count,
    avg(video_sec_sum_1d/(video_user_count_1d)) over(partition by chapter_id,chapter_name order by chapter_id) video_sec_user_avg,
    video_user_count_1d video_user_count
from ${APP}.dws_process_user_chapter_1d
where dt = '$do_date'
union all
select
    dt,
    recent_days,
    chapter_id,
    chapter_name,
    case recent_days
        when 7 then video_count_7d
        when 30 then video_count_30d
    end video_count,
    case recent_days
        when 7 then
        avg(video_sec_sum_7d/(video_user_count_7d)) over(partition by chapter_id,chapter_name order by chapter_id)
        when 30 then
        avg(video_sec_sum_30d/(video_user_count_30d)) over(partition by chapter_id,chapter_name order by chapter_id)
    end video_sec_user_avg,
    case recent_days
        when 7 then video_user_count_7d
        when 30 then video_user_count_30d
    end video_user_count
from ${APP}.dws_process_user_chapter_nd lateral view explode(array(7,30)) tmp as recent_days
where dt = '$do_date';"
ads_video_stats_by_course="
insert overwrite table ${APP}.ads_video_stats_by_course
select * from ${APP}.ads_video_stats_by_course
union
select
    dt,
    1 recent_days,
    course_id,
    course_name,
    video_count_1d video_count,
    avg(video_sec_sum_1d/(video_user_count_1d)) over(partition by course_id,course_name order by course_id) video_sec_user_avg,
    video_user_count_1d video_user_count
from ${APP}.dws_process_user_course_1d
where dt = '$do_date'
union all
select
    dt,
    recent_days,
    course_id,
    course_name,
    case recent_days
        when 7 then video_count_7d
        when 30 then video_count_30d
    end video_count,
    case recent_days
        when 7 then
        avg(video_sec_sum_7d/(video_user_count_7d)) over(partition by course_id,course_name order by course_id)
        when 30 then
        avg(video_sec_sum_30d/(video_user_count_30d)) over(partition by course_id,course_name order by course_id)
    end video_sec_user_avg,
    case recent_days
        when 7 then video_user_count_7d
        when 30 then video_user_count_30d
    end video_user_count
from ${APP}.dws_process_user_course_nd lateral view explode(array(7,30)) tmp as recent_days
where dt = '$do_date';"
ads_new_buyer_stats="
insert overwrite table ${APP}.ads_new_buyer_stats
select *
from ${APP}.ads_new_buyer_stats
union
select '$do_date',
       recent_days,
       sum(if(order_date_first >= date_add('$do_date', -recent_days + 1), 1, 0)) new_order_user_count
from ${APP}.dws_trade_user_order_td lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt = '$do_date'
    group by recent_days;"

ads_age_order_stats="
insert overwrite table  ${APP}.ads_age_order_stats
select *
from ${APP}.ads_age_order_stats
union
select
       '$do_date',
       recent_days,
       age_segment,
       sum(1)
       from
(select t1.user_id,
       t1.recent_days,
       concat(substr(birthday,3,1) , '0后') age_segment
from (select user_id,
             1 recent_days
      from ${APP}.dws_trade_user_order_1d
    where  dws_trade_user_order_1d.dt = '$do_date'
     ) t1
         join ${APP}.dim_user_zip t2
                   on t1.user_id = t2.id) t3
group by age_segment,recent_days
union all
select
       '$do_date',
       recent_days,
       age_segment,
       sum(1)
       from
(select t1.user_id,
       t1.recent_days,
       concat(substr(birthday,3,1) , '0后') age_segment
from (select user_id,
             7 recent_days
      from ${APP}.dws_trade_user_order_nd
    where dws_trade_user_order_nd.order_count_7d > 0
    and dws_trade_user_order_nd.dt= '$do_date'
     ) t1
         left join ${APP}.dim_user_zip t2
                   on t1.user_id = t2.id) t3
group by age_segment,recent_days
union all
select
       '$do_date',
       recent_days,
       age_segment,
       sum(1)
       from
(select t1.user_id,
       t1.recent_days,
       concat(substr(birthday,3,1) , '0后') age_segment
from (select user_id,
             30 recent_days
      from ${APP}.dws_trade_user_order_nd
    where dws_trade_user_order_nd.dt = '$do_date'

     ) t1
         left join ${APP}.dim_user_zip t2
                   on t1.user_id = t2.id) t3
group by age_segment,recent_days;"

case $1 in
    "ads_user_change" )
        hive -e "$ads_user_change"
    ;;
    "ads_user_retention" )
        hive -e "$ads_user_retention"
    ;;
    "ads_trade_stats" )
        hive -e "$ads_trade_stats"
    ;;
    "ads_order_by_province" )
        hive -e "$ads_order_by_province"
    ;;
    "ads_traffic_stats_by_channel" )
        hive -e "$ads_traffic_stats_by_channel"
    ;;
    "ads_page_path" )
        hive -e "$ads_page_path"
    ;;
"ads_user_action" )
        hive -e "$ads_user_action"
    ;;
    "ads_video_stats_by_chapter" )
        hive -e "$ads_video_stats_by_chapter"
    ;;
    "ads_video_stats_by_course" )
        hive -e "$ads_video_stats_by_course"
    ;;
    "ads_new_buyer_stats" )
        hive -e "$ads_new_buyer_stats"
    ;;
    "ads_age_order_stats" )
        hive -e "$ads_age_order_stats"
;;
"all" ) hive -e "$ads_user_change$ads_user_retention$ads_trade_stats$ads_order_by_province$ads_traffic_stats_by_channel$ads_traffic_stats_by_channel$ads_page_path$ads_user_action$ads_video_stats_by_chapter$ads_video_stats_by_course$ads_new_buyer_stats$ads_age_order_stats"
    ;;
esac
